﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace HenIT.DatabaseTools
{
    public class Program
    {
        static void Main(string[] args)
        {
            if (args.Length < 4 || CommandLineUtils.IsCommand(args, "/?", "-?", "/h", "-h"))
            {
                ShowHelp();
            }
            else
            {
                string server = args[0];
                string database = args[1];
                string dbPath = args[2];
                bool dropDB = CommandLineUtils.IsCommand(args, "/d", "-d");
                bool dropoverride = CommandLineUtils.IsCommand(args, "/dropoverride", "-dropoverride");
                string[] scripts = (from a in args
                                    where a.ToLower().EndsWith(".sql")
                                    select a).ToArray();
                try
                {
                    if (dropDB || dropoverride)
                        DropDB(server, database, dropoverride);
                    CreateDB(server, database, dbPath, scripts);
                }
                catch (Exception ex)
                {
                    if (!ex.Message.Contains("Terminating application"))
                        Console.WriteLine(ex.Message);
                }
                Console.WriteLine("Done");
            }
        }

        private static void ShowHelp()
        {
            Console.WriteLine("Usage: BuildDB.exe <server> <database> <path> <-d|-dropoverride> <script1.sql [script2.sql ...]>");
            Console.WriteLine("   Where");
            Console.WriteLine("     -d            : drop database first");
            Console.WriteLine("     -dropoverride : drop database first without prompting");
        }


        static void DropDB(string instanceName, string database, bool dropwithDB)
        {
            string lastStep = "CreateDB";
            try
            {
                if (!dropwithDB)
                    Console.WriteLine("Are you sure you want to drop the existing database? (Y/N)\r\nMake sure you have a WORKING BACKUP of the database if you want to recover it!");
                if (dropwithDB || Console.ReadKey().Key == ConsoleKey.Y)
                {
                    Console.WriteLine("");
                    System.Data.SqlClient.SqlConnectionStringBuilder sb = new System.Data.SqlClient.SqlConnectionStringBuilder();

                    sb.DataSource = instanceName;
                    sb.InitialCatalog = "master";
                    sb.IntegratedSecurity = true;
                    using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(sb.ConnectionString))
                    {
                        conn.Open();
                        string currentScriptText = Properties.Resources.DropDbTemplate
                            .Replace("%DBNAME%", database);
                        Console.WriteLine("Dropping database '{0}' on '{1}'", database, instanceName);
                        RunScript(conn, currentScriptText);
                    }
                }
            }
            catch (Exception outerEx)
            {
                Console.WriteLine("Last step:{0}\r\n{1}", lastStep, outerEx.ToString());
            }
        }
        
        static void CreateDB(string instanceName, string database, string databasePath, string[] scripts)
        {
            string lastStep = "CreateDB";
            string currentScriptText;
            try
            {
                System.Data.SqlClient.SqlConnectionStringBuilder sb = new System.Data.SqlClient.SqlConnectionStringBuilder();

                sb.DataSource = instanceName;
                sb.InitialCatalog = "master";
                sb.IntegratedSecurity = true;
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(sb.ConnectionString))
                {
                    conn.Open();
                    currentScriptText = Properties.Resources.CreateDBTemplate
                        .Replace("%DBNAME%", database)
                        .Replace("%DBPATH%", databasePath);
                    RunScript(conn, currentScriptText);                    
                }
                sb.InitialCatalog = database;
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(sb.ConnectionString))
                {
                    conn.Open();
                    lastStep = "Running scripts";
                    Console.WriteLine("Running scripts");
                    foreach (string script in scripts)
                    {
                        Console.WriteLine(" {0}", script);
                        if (System.IO.File.Exists(script))
                        {
                            currentScriptText = System.IO.File.ReadAllText(script);
                            RunScript(conn, currentScriptText);
                        }
                        else
                        {
                            Console.WriteLine("Count not find the script '{0}'", script);
                        }
                    }
                }
            }
            catch (Exception outerEx)
            {
                Console.WriteLine("Last step:{0}\r\n{1}", lastStep, outerEx.ToString());               
            }
        }

        private static void RunScript(System.Data.SqlClient.SqlConnection conn, string currentScriptText)
        {
            string[] sqlCommands = currentScriptText.Split(new string[] { "\r\nGO" }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string sqlCommand in sqlCommands)
            {
                try
                {
                    using (System.Data.SqlClient.SqlCommand cmnd = new System.Data.SqlClient.SqlCommand(sqlCommand, conn))
                    {
                        cmnd.ExecuteNonQuery();
                    }                    
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error running script: {0}\r\n{1}", sqlCommand, ex.ToString());
                }
            }
        }
    }
}
